package com.ratingbay.console.naivequery;

import java.util.*;
import java.math.BigDecimal;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.EntityTransaction;

import org.springframework.data.domain.Page;

import com.ratingbay.console.model.*;

public class LinkNaiveQuery{
	private LinkNaiveQuery(){
	}
	private static LinkNaiveQuery instance = null;
    public static LinkNaiveQuery getInstance(){
    	if(null == instance){
    		instance = new LinkNaiveQuery();
    	}
        return instance;
    }

    //used in class inner
    protected String getIdsStr(List<Long> idList){
        String idsStr = "(";
        Iterator<Long> it = idList.iterator();
        while(it.hasNext()){
            idsStr += it.next();
            if(it.hasNext()) idsStr += ",";
        }
        idsStr += ")";
        return idsStr;
    }
    

    public List<Long> findByTypeAndGameAndLinkIdsGroupByTweetCountDescLimit(Integer type, Long idGame,
			List<Long> tUserIds,int pageSize,int pageNum) {
        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();
        /**
         * select min(l.id) from link l,(select * from tweet_link where id_game=75 limit 500) tl,(select * from tweet where user_id in (2291824746,36969785))t where tl.id_link=l.id and  t.id=tl.id_tweet and l.type= 2 and l.id_game= 75 and media_direct_link is not null group by media_direct_link order by max(tweet_count) desc limit 10 offset 0
         */
        Long limit = (long)pageSize;
        Long offset = (long)pageNum * pageSize;
        int types = (int)type;
        String tUserIdsStr = getIdsStr(tUserIds);

        String sql = "select min(l.id) from link l,(select * from tweet_link where id_game=" + idGame + " limit 200) tl,"
        		+ "(select * from tweet where user_id in "+ tUserIdsStr +") t where tl.id_link=l.id and  t.id=tl.id_tweet "
        				+ "and l.type= " + types + " and l.id_game= " + idGame + " and media_direct_link is not null group by media_direct_link "
        				+ "order by max(tweet_count) desc limit " + limit +  " offset " + offset;
        Query query =  em.createNativeQuery(sql);
		List<Long> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }

    //for cjob
    public List<Long> getLinkIds(int pageNum, int pageSize){
        Long count = null;

        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        Long limit = (long)pageSize;
        Long offset = (long)pageNum * pageSize;

        String sql = "SELECT id FROM link ORDER BY id ASC LIMIT " + limit + " OFFSET " + offset;
        Query query =  em.createNativeQuery(sql);

        List<Long> objectArrayList = query.getResultList();
        em.close();
        return objectArrayList;
    }

    //cjob entry
    public int updateTweetCountPageNum = 0;
    public int updateTweetCountPageSize = 2000;
    public void updateTweetCount() {
        System.out.println("CJOB start updateTweetCount");

        EntityManagerFactory emf = NaiveQuery.getInstance().getEntityManagerFactory();
        EntityManager em = emf.createEntityManager();

        int pageNum = this.updateTweetCountPageNum;
        int pageSize = this.updateTweetCountPageSize;
        
        List<Long> linkIds = this.getLinkIds(pageNum, pageSize);
        Iterator<Long> it = linkIds.iterator();
        Long linkId;
        Long tweetCount;
        String sql;
        Query query ;
        while(it.hasNext()){
            linkId = it.next();

            sql = "SELECT count(*) FROM tweet_link WHERE id_link = " + linkId;
            query = em.createNativeQuery(sql);
            tweetCount = (Long)query.getSingleResult();

            sql = "UPDATE link SET tweet_count = " + tweetCount + " WHERE id = " + linkId;
            EntityTransaction tx = em.getTransaction();
            tx.begin();
            query = em.createNativeQuery(sql);
            query.executeUpdate();
            tx.commit();
        }
        if(linkIds.size() < 1){
            this.updateTweetCountPageNum = 0;
            System.out.println("CJOB updateTweetCount all once");
        }else{
            this.updateTweetCountPageNum ++;
        }

        System.out.println("CJOB end updateTweetCount");
    }
}